Plotly is a Python library for creating interactive and dynamic visualizations in various formats, such as graphs, charts, and maps. It offers a high-level API for generating rich and customizable visualizations with ease.

The nice thing that this provides is the ability to show a features attribute data when you hover over it, just like you could with charting tabular data. And of course you can pan and zoom in and out as well.

Very aesthetic Choropleth Maps can be prepared with Plotly. However, I prefer to GeoPandas and Folium for this purpose. It gives you more control over the map elements.

Get the code from my GitHub repository

Importing required libraries:

%matplotlib inline
import psycopg2
import psycopg2.extras
import plotly
import plotly.graph_objects as go
import plotly.express as px
import pandas as pd
import geopandas as gpd

Establishing connection with my PostgreSQL server. Keeping my credentials secret for security reasons. I always prefer to use the data stored in my server. You can easily retrieve and manipulate data with SQL.

df = pd.read_csv(r"D:\Dropbox\Touhid Personal\credentials\postgresql_credentials.csv")

conn = psycopg2.connect (
    host = df.loc[0,'host'],
    port = df.loc[0,'port'],
    dbname = df.loc[0,'database'],
    user = df.loc[0,'username'],
    password = df.loc[0,'password']
)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

Example from Plotly

This code has been taken from the official website of Plotly. It creates a Choropleth map showing the unemployment rate in USA. I will try to create similar map for my study area.

from urllib.request import urlopen
import json
with urlopen('https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json') as response:
    counties = json.load(response)

import pandas as pd
df = pd.read_csv("https://raw.githubusercontent.com/plotly/datasets/master/fips-unemp-16.csv",
                   dtype={"fips": str})

import plotly.express as px

fig = px.choropleth_mapbox(df, geojson=counties, locations='fips', color='unemp',
                           color_continuous_scale="Viridis",
                           range_color=(0, 12),
                           mapbox_style="carto-positron",
                           zoom=3, center = {"lat": 37.0902, "lon": -95.7129},
                           opacity=0.5,
                           labels={'unemp':'unemployment rate'}
                          )
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
fig.show()

Choropleth Map 1

In the choropleth map, I have tried to display the variation in average biled amount for the DMAs of Dhaka WASA. Please note that this display is not based on actual data.

Here I have used two different tables from server. Table 1 (titled "all_dma") contains the geometry data, and Table 2 (titled "hcl") contains the billing data. Therefore, I had to join these two tables and keep the data in a Pandas Dataframe for mapping.

#Table 1
#using GeoPandas read_postgis method
all_dma_data = gpd.read_postgis("SELECT * FROM all_dma", conn, geom_col='geom')

#Table 2
cur = conn.cursor()
cur.execute("""
    SELECT hcl_dma_id, AVG(billed_amt) AS avg_billed_amt
    FROM hcl
    GROUP BY hcl_dma_id;
""")
hcl_data = cur.fetchall()

# Pandas DataFrame
hcl_df = pd.DataFrame(hcl_data, columns=['hcl_dma_id', 'avg_billed_amt'])

# Converting 'avg_billed_amt' column to numeric and handling missing values (if any)
hcl_df['avg_billed_amt'] = pd.to_numeric(hcl_df['avg_billed_amt'], errors='coerce')
hcl_df.dropna(subset=['avg_billed_amt'], inplace=True)

# Merging Table1 and Table2 data
merged_data = all_dma_data.merge(hcl_df, left_on='dma_id', right_on='hcl_dma_id', how='left')

#Mapping
fig = px.choropleth_mapbox(merged_data, geojson=merged_data['geom'], locations=merged_data.index,
                    color='avg_billed_amt', color_continuous_scale='deep',
                           mapbox_style="carto-positron", zoom=10.5, center = {"lat": 23.8103, "lon": 90.4125},
                           opacity = 0.8,
                    labels={'avg_billed_amt': 'Average Billed Amount'},
                    title='Choropleth Map: Average Billed Amount by DMA')


fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})

fig.show()

Choropleth Map 2

The approach is same for this map. Using the same two tables. This time showing the average pipe length for consumer service connection.

all_dma_data = gpd.read_postgis("SELECT * FROM all_dma", conn, geom_col='geom')

cur = conn.cursor()
cur.execute("""
    SELECT hcl_dma_id, AVG(hcl_length) AS avg_hcl_length
    FROM hcl
    GROUP BY hcl_dma_id;
""")
hcl_data = cur.fetchall()

hcl_df = pd.DataFrame(hcl_data, columns=['hcl_dma_id', 'avg_hcl_length'])

hcl_df['avg_hcl_length'] = pd.to_numeric(hcl_df['avg_hcl_length'], errors='coerce')
hcl_df.dropna(subset=['avg_hcl_length'], inplace=True)

merged_data = all_dma_data.merge(hcl_df, left_on='dma_id', right_on='hcl_dma_id', how='left')

fig = px.choropleth_mapbox(merged_data, geojson=merged_data['geom'], locations=merged_data.index,
                    color='avg_hcl_length', color_continuous_scale='deep',
                           mapbox_style="carto-positron", zoom=10.5, center = {"lat": 23.8103, "lon": 90.4125},
                           opacity = 0.8,
                    labels={'avg_hcl_length': 'Average SC Length(m)'},
                    title='Choropleth Map: Average Billed Amount by DMA')


fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})

fig.show()

Prepared By: Md. Touhidur Rahman, Email: touhidur002@gmail.com